package dao;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DBManager {

	private static DBManager _instance;
	private static Connection conn = null;
	private FileInputStream in;
	private Properties props;

	private DBManager() throws SQLException {
		if (!dbExists()) {
			System.err.println("De database bestaat niet");
		}
		else {
			
		}
	}

	public static synchronized DBManager getInstance() throws SQLException {
		if (_instance == null) {
			_instance = new DBManager();
		}
		return _instance;
	}

	public Connection getConnection() {

		return conn;
	}

	public void close() {

		try {
			conn.close();
			_instance = null;
			conn = null;
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	private boolean dbExists() throws SQLException {
		boolean exists = false;

		props = new Properties();
		try {
			in = new FileInputStream("database.properties");
			props.load(in);
			in.close();

			String drivers = props.getProperty("jdbc.drivers");
			if (drivers != null) {
				System.setProperty("jdbc.drivers", drivers);
				String hostname = props.getProperty("jdbc.hostname");
				String database = props.getProperty("jdbc.database");
				String username = props.getProperty("jdbc.username");
				String password = props.getProperty("jdbc.password");

				if (drivers.equalsIgnoreCase("com.mysql.jdbc.Driver")) {
					conn = DriverManager.getConnection(hostname, username,
							password);
					String qExists = "SHOW DATABASES LIKE ?";
					String qMakeDB = "CREATE DATABASE weer";
					PreparedStatement st = conn.prepareStatement(qExists);
					PreparedStatement mkdb = conn.prepareStatement(qMakeDB);
					st.setString(1, database);
					ResultSet rs = st.executeQuery();

					if (rs.next() == false) {
						// De database bestaat niet, maar de server wel
						exists = false;

						// Maak de database aan
						mkdb.execute();

						// Verbind met de nieuwe database
						conn.close();
						conn = DriverManager.getConnection(hostname + "/"
								+ database, username, password);

						// Importeer de data vanuit een csv bestand
						CSVImporter importer = new CSVImporter(
								"knmigegevens.txt", conn);
						importer.importMetingen();

						exists = true;
					} else {
						exists = true;
						conn = DriverManager.getConnection(hostname + "/"
								+ database, username, password);
					}

				} else if (drivers.equalsIgnoreCase("org.postgresql.Driver")) {
					conn = DriverManager.getConnection(hostname, username,
							password);
					String qExists = "select count(*) from pg_catalog.pg_database where datname = 'weer'";
					String qMakeDB = "CREATE DATABASE weer";
					Statement st = conn.createStatement();
					Statement mkdb = conn.createStatement();

					ResultSet rs = st.executeQuery(qExists);
					rs.next();
					if (rs.getInt(1) == 0) {

						exists = false;

						// Maak de database aan
						mkdb.execute(qMakeDB);
						conn.close();
						// Verbind met de database
						conn = DriverManager.getConnection(hostname + "/"
								+ database, username, password);

						// Importeer de data vanuit een csv bestand
						CSVImporter importer = new CSVImporter(
								"knmigegevens.txt", conn);
						importer.importMetingen();

						exists = true;
					} else {
						exists = true;
						conn = DriverManager.getConnection(hostname + "/"
								+ database, username, password);
					}
				} else if (drivers
						.equalsIgnoreCase("org.apache.derby.jdbc.EmbeddedDriver")) {
					exists = false;

					conn = DriverManager
								.getConnection(hostname+ ":" + database
										+ ";create=true");
					
					// Controlleer of de database leeg is
					try {
						Statement st = conn.createStatement();
						st.executeQuery("select * from station");
						exists = true;
					} catch( SQLException e) {
						exists = false;
					}
					
					// Indien de database leeg is:
					if (exists == false) {
						// Importeer de data vanuit een csv bestand
						CSVImporter importer = new CSVImporter(
								"knmigegevens.txt", conn);
						importer.importMetingen();
						exists = true;
					}
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			throw e;
		}

		return exists;
	}
}
